import dataset
import logging
import tools.file_tools
import pandas as pd
from dataset.database import Database
from tools.file_tools import get_filenames_in_folder_with_check, extract_parts_from_filename, get_文档_页数

logging.basicConfig(
    level=logging.DEBUG,
    format='%(levelname)8s: %(message)s'
)

文档 = '([^-\.]+)'
班级 = '([^-\.]+)'
学号 = '([0-9]{11})'
姓名 = '([^-\.]+)'
日期 = '([0-9]{4})'
后缀 = '([^-\.]+)'


def load_学生名单_into_DB(db: Database, name_sheet_xlsx_path: str):
    df = pd.read_excel(
        name_sheet_xlsx_path,
        usecols=['学号', '姓名', '班级'],  # type: ignore
        dtype={'学号': int, '姓名': str, '班级': str},
        header=0
    )

    table = db['学生名单']  # 获取或创建 student 表
    existing_nos = [row['学号'] for row in table.find()]  # 提取所有已存在的学号

    # 筛选出 DataFrame 中不存在于数据库的新记录
    new_records = df[~df['学号'].isin(existing_nos)].to_dict('records')
    # ~ 表示“非”，isin(existing_nos) 检查学号是否在已存在列表中

    if new_records:
        table.insert_many(new_records)
        logging.info(f"成功插入 {len(new_records)} 条学生记录")
    else:
        logging.info("所有记录已存在，无需插入")


def load_实践报告_into_DB(db: Database, files_in_dir: str, file_types: [], format_pattern: str):
    se_filenames = get_filenames_in_folder_with_check(
        files_in_dir,
        file_types,
        format_pattern
    )

    se_学号 = extract_parts_from_filename(se_filenames, format_pattern, 3).astype(int)
    # se_姓名 = cls.extract_parts_from_filename(se_filenames, PATTERN_报告, 4).astype(str)
    se_日期 = extract_parts_from_filename(se_filenames, format_pattern, 5).astype(str)
    se_页数 = get_文档_页数(files_in_dir, se_filenames)

    df = pd.DataFrame({
        '学号': se_学号,
        '日期': se_日期,
        '页数': se_页数
    })

    table = db['报告提交']  # 获取或创建表
    existing_nos = [row['学号'] for row in table.find()]  # 提取所有已存在的学号

    # 筛选出 DataFrame 中不存在于数据库的新记录
    new_records = df[~df['学号'].isin(existing_nos)].to_dict('records')

    if new_records:
        table.insert_many(new_records)
        logging.info(f"成功插入 {len(new_records)} 条报告提交记录")
    else:
        logging.info("所有记录已存在，无需插入")


if __name__ == "__main__":
    BASE_PATH = r"D:\100-Project\2025-2026-1学期安排\首义学院\19201018_《软件工程综合实践（创新创业）（企业）》"
    FOLDER_PATH = f"{BASE_PATH}\学生提交\实习报告"
    FILE_TYPES = ['.doc', '.docx']
    PATTERN_报告_0 = f'^{文档}-{班级}-{学号}-{姓名}.{日期}.{后缀}$'
    PATTERN_报告_1 = f'^{文档}-{班级}-{学号}-{姓名}.{后缀}$'

    # 数据库路径
    db_path = r'sqlite/data.db'
    names_xlsx_path = f"{BASE_PATH}/学生提交/学生名单.xlsx"

    db = dataset.Database(f'sqlite:///{db_path}')

    load_学生名单_into_DB(db, names_xlsx_path)
    load_实践报告_into_DB(db, FOLDER_PATH, FILE_TYPES, PATTERN_报告_0)
